package edu.mtu.citizenscience.cs.sdk.uploader.json;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

import android.database.Cursor;
import edu.mtu.citizenscience.cs.sdk.uploader.db.UploaderDBInterface;
import edu.mtu.citizenscience.cs.sdk.uploader.json.exception.InvalidRelationship;
import edu.mtu.citizenscience.cs.sdk.uploader.model.Entry;
import edu.mtu.citizenscience.cs.sdk.uploader.model.EntryType;
import edu.mtu.citizenscience.cs.sdk.uploader.model.Row;
import edu.mtu.citizenscience.cs.sdk.uploader.model.entry.FileEntry;
import edu.mtu.citizenscience.cs.sdk.uploader.model.entry.FileType;
import edu.mtu.citizenscience.cs.sdk.uploader.model.entry.Location;
import edu.mtu.citizenscience.cs.sdk.uploader.model.entry.LocationEntry;
/**
 * @author Corbin Uselton <corbinu@cwuselto@mtu.edu>
 * @copyright (c) 2012 MichiganTech <http://www.mtu.edu>
 * @since 1.0
 * 
 * A table in the apps database
 */
public class Table {
	// name of the table
	private String name;
	// name of the parent table if any
	private String parentTable;
	// type of file stored in this table if any
	private FileType fileType;
	// array of columns in this table
	private Column[] columns;
	
	/**
	 * Get the table name
	 * 
	 * @param   void
	 * @return	String - the table name
	 */
	public String getName()
	{
		return this.name;
	}
	
	/**
	 * Set the table name
	 * 
	 * @param   name - String - the table name
	 * @return	void
	 */
	public void setName(String name)
	{
		this.name = name;
	}
	
	/**
	 * Get the table columns
	 * 
	 * @param   void
	 * @return	Column[] - the table columns
	 */
	public Column[] getColumns()
	{
		return this.columns;
	}
	
	/**
	 * Set the table columns
	 * 
	 * @param   columns - Column[] - the table columns
	 * @return	void
	 */
	public void setColumns(Column[] columns)
	{
		this.columns = columns;
	}
	
	/**
	 * Get the table file type
	 * 
	 * @param   void
	 * @return	FileType - table file type
	 */
	public FileType getFileType()
	{
		return this.fileType;
	}
	
	/**
	 * Set the table file type
	 * 
	 * @param   type - FileType - table file type
	 * @return	void
	 */
	public void setFileType(FileType type)
	{
		this.fileType = type;
	}
	
	/**
	 * Get the parent table
	 * 
	 * @param   void
	 * @return	String - parent table
	 */
	public String getParentTable()
	{
		return this.parentTable;
	}
	
	/**
	 * Set the parent table
	 * 
	 * @param   table - String - parent table
	 * @return	void
	 */
	public void setParentTable(String table)
	{
		this.parentTable = table;
	}
	
	/**
	 * Get the table rows stored in the app DB
	 * 
	 * @param   db - UploaderDBInterface - the DB interface
	 * @param   tables - Table[] - the array of DB tables
	 * @return	List<Row> - table rows
	 * @throws InvalidRelationship
	 */
	public List<Row> getRows(UploaderDBInterface db, Table[] tables) throws InvalidRelationship
	{
		// new array list of to store the rows
		List<Row> rows = new ArrayList<Row>();
		
		// query the DB looking for any rows that have not been uploaded already
		Cursor cursor = db.getDB().query(this.name, null, "_UPLOADED = 0", null, null, null, null);
		
		// move the cursor to the start of the rows
		cursor.moveToFirst();
		
		// loop over all rows in the cursor
		while ( ! cursor.isAfterLast())
		{
			// get the row for for this cursor
			rows.add(this.getRow(db, tables, cursor));
			// move on to the next row
			cursor.moveToNext();
		}
		// close the DB cursor
		cursor.close();
		
		// return the list of rows
		return rows;
	}
	
	/**
	 * Get the table row for the passed cursor
	 * 
	 * @param   db - UploaderDBInterface - the DB interface
	 * @param   tables - Table[] - the array of DB tables
	 * @param	cursor - Cursor - the cursor to load the row data from
	 * @return	Row - row
	 * @throws InvalidRelationship
	 */
	public Row getRow(UploaderDBInterface db, Table[] tables, Cursor cursor) throws InvalidRelationship
	{
		// create a new row
		Row row = new Row();
		
		// set the row table name to the current table
		row.setTable(this.name);
		
		// create a new location object
		Location location  = new Location();
		// create a new list place the row entries in
		List<Entry<?>> entries = new ArrayList<Entry<?>>();
		// loop over the table columns
		for (Column column: this.columns)
		{
			// check if the column is the special type id
			if (column.getType() == EntryType.TYPE_ID)
			{
				// set the row id as the id column value
				row.setId(cursor.getLong(cursor.getColumnIndex(column.getName())));
			}
			// check if the column is the special type file
			else if (column.getType() == EntryType.TYPE_FILE)
			{
				// set the row file entry to a new file from the file path in the file column
				row.setFileEntry(new FileEntry(column.getName(), this.fileType, new File(cursor.getString(cursor.getColumnIndex(column.getName())))));
			}
			// check if the column is the special type latitude
			else if (column.getType() == EntryType.TYPE_LATITUDE)
			{
				// set the location latitude to the double from the latitude column
				location.setLatitude(cursor.getDouble(cursor.getColumnIndex(column.getName())));
			}
			// check if the column is the special type longitude
			else if (column.getType() == EntryType.TYPE_LONGITUDE)
			{
				// set the location longitude to the double from the latitude column
				location.setLongitude(cursor.getDouble(cursor.getColumnIndex(column.getName())));
			}
			else
			{
				// get the entry in the column at this row
				Entry<?> entry = column.getEntry(cursor);
				// check that the entry is no empty
				if (entry != null)
				{
					// add the entry to the entry list
					entries.add(entry);
				}
			}
		}
		
		// set the row entries to the newly populated entry list
		row.setEntries(entries);
		// check if got a longitude and latitude value
		if ((location.getLatitude() != null) && (location.getLongitude() != null))
		{	
			// set the row location to a new location entry with the longitude and latitude
			row.setLocationEntry(new LocationEntry(location));
		}
		
		// create a new row list to hold this row's child rows
		List<Row> children = new ArrayList<Row>();
		// loop over the other tables
		for (Table table : tables)
		{
			// check the the table has a this table as its parent
			if (this.name.equals(table.getParentTable()))
			{
				// get the child rows from the child table and add the children list
				children.addAll(table.getChildRows(db, tables, row.getId()));
			}
		}
		// attach the child rows to the current row
		row.setChildren(children);
		
		// return the row
		return row;
	}

	/**
	 * Get child rows in a child table for a specific row id
	 * 
	 * @param   db - UploaderDBInterface - the DB interface
	 * @param   tables - Table[] - the array of DB tables
	 * @param	id - long - the parent row id
	 * @return	List<Row> - list of child rows
	 * @throws InvalidRelationship
	 */
	public List<Row> getChildRows(UploaderDBInterface db, Table[] tables, long id) throws InvalidRelationship
	{
		// create a new row list to store the child rows
		List<Row> rows = new ArrayList<Row>();
		
		// loop over the tables columns
		for (Column column: this.columns)
		{
			// check if the column is the type parent id
			if (column.getType() == EntryType.TYPE_PARENT_ID)
			{
				// query this table for rows which have the id as their parent id
				Cursor cursor = db.getDB().query(this.name, null, column.getName() + " = ?", new String[]{id+""}, null, null, null);
				
				// move the cursor to the beginning of the rows
				cursor.moveToFirst();
				
				// loop over the cursor rows
				while ( ! cursor.isAfterLast())
				{
					// get the row from the cursor and add to the rows list
					rows.add(this.getRow(db, tables, cursor));
					
					// move to the next row
					cursor.moveToNext();
				}
				// close the cursor
				cursor.close();
				
				// return the child rows
				return rows;
			}
		}
		
		// should have returned row list by now so the table specifies an invalid parent relationship
		// throw InvalidRelationship with the current table's name
		throw new InvalidRelationship(this.name);
	}
	
}
